Condition for Creating Updatable VIEW¶

A virtual table is updatable if it satisfies the following conditions:

• The FROM clause must include the updatable table or view only.

  In versions lower than CUBRID 9.0, only one updatable table can be included to the FROM clause it requires. However, two tables in parentheses like FROM (class_x, class_y) can be updated since the two were expressed as one table. In version of CUBRID 9.0 or higher, more than one updatable table is allowed. The FROM clause must include only one table or updatable view. However, two tables included in parentheses as in FROM (class_x, class_y) can be updated because they represent one table.

• A JOIN syntax can be included.

  Note

  In versions lower than CUBRID 10.0, you cannot update a view which is created with a JOIN syntax.

• The DISTINCT or UNIQUE statement should not be included.

• The GROUP BY... HAVING statement should not be included.

• Aggregate functions such as SUM or AVG should not be included.

• The entire query must consist of queries that can be updated by UNION ALL, not by UNION. However, the table should exist only in one of the queries that constitute UNION ALL.

• If a record is inserted into a view created by using the UNION ALL statement, the system determines into which table the record will be inserted. This cannot be done by the user. To control this, the user must manually insert the row or create a separate view for insertion.

Even when all rules above are satisfied, columns that contains following contents cannot be updated.

• Path expressions (example: tbl_name.col_name)
• Numeric type column that includes an arithmetic operator

Even though the column defined in the view is updatable, a view can be updated only when an appropriate update authorization is granted on the table included in the FROM clause. Also there must be an access authorization to a view. The way to grant an access authorization to a view is the same to grant an access authorization to a table. For details on granting authorization, see GRANT.

View's COMMENT¶

You can specify a view's comment as follows.

CREATE OR REPLACE VIEW b_view AS SELECT * FROM a_tbl ORDER BY id DESC COMMENT 'changed view';

You can see the specified comment of a view by running this syntax.

SHOW CREATE VIEW view_name;
SELECT vclass_name, comment from db_vclass;

Or you can see the view's comment with ;sc command which displays the schema in the CSQL interpreter.

$ csql -u dba demodb

csql> ;sc b_view

Also, you can add a comment for each column of the view.

CREATE OR REPLACE VIEW b_view (a COMMENT 'a comment', b COMMENT 'b comment')
AS SELECT * FROM a_tbl ORDER BY id DESC COMMENT 'view comment';

To see how to change a comment of a view, refer to ALTER VIEW syntax on the below.

ADD QUERY Clause¶

You can add a new query to a query specification by using the ADD QUERY clause of the ALTER VIEW statement. 1 is assigned to the query defined when a virtual table was created, and 2 is assigned to the query added by the ADD QUERY clause.

ALTER [VIEW | VCLASS] view_name
ADD QUERY <select_statement>
[INHERIT <resolution> , ...] ;

    <resolution> ::= {column_name} OF superclass_name [AS alias]

• view_name: specifies the name of a view where the query to be added.
• <select_statement>: specifies the query to be added.

SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL

ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL
            1  '111-1111'
            2  '222-2222'

AS SELECT Clause¶

You can change the SELECT query defined in the virtual table by using the AS SELECT clause in the ALTER VIEW statement. This function is working like the CREATE OR REPLACE statement. You can also change the query by specifying the query number 1 in the CHANGE QUERY clause of the ALTER VIEW statement.

ALTER [VIEW | VCLASS] view_name AS <select_statement> ;

• view_name: specifies the name of a view to be modified.
• <select_statement>: specifies the new query statement to replace the SELECT statement defined when a view is created.

ALTER VIEW b_view AS SELECT * FROM a_tbl WHERE phone IS NOT NULL;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'

CHANGE QUERY Clause¶

You can change the query defined in the query specification by using the CHANGE QUERY clause reserved word of the ALTER VIEW statement.

ALTER [VIEW | VCLASS] view_name
CHANGE QUERY [integer] <select_statement> ;

• view_name: specifies the name of a view to be modified.
• integer: specifies the number value of the query to be modified. The default value is 1.
• <select_statement>: specifies the new query statement to replace the query whose query number is integer.

--adding select_statement which query number is 2 and 3 for each
ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);
ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id = 3;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'

--altering view changing query number 2
ALTER VIEW b_view CHANGE QUERY 2 SELECT * FROM a_tbl WHERE phone IS NULL;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL
            4  NULL
            5  NULL
            3  '333-3333'

DROP QUERY Clause¶

You can drop a query defined in the query specification by using the DROP QUERY of the ALTER VIEW statement.

ALTER VIEW b_view DROP QUERY 2,3;
SELECT * FROM b_view;

           id  phone
===================================
            1  '111-1111'
            2  '222-2222'
            3  '333-3333'
            4  NULL
            5  NULL

COMMENT Clause¶

You can change a view's comment with COMMENT clause of ALTER VIEW syntax.

ALTER [VIEW | VCLASS] view_name COMMENT [=] 'view_comment';

ALTER VIEW b_view COMMENT = 'changed view comment';